package ${model.packageName}.${model.modulePackage}.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.util.StringUtils;

import com.apa.biz.common.CustomJdbcTemplate;
import com.apa.biz.common.CustomRowBounds;

import ${model.packageName}.${model.modulePackage}.entity.${model.model}Entity; 
 



/**
* ${model.model}DaoImpl: ${model.comment} 数据持久层接口实现
* @author ${model.author} ${date}
*
*/
public class ${model.model}DaoImpl {
	private Logger logger = LoggerFactory.getLogger(${model.model}DaoImpl.class);

	@Autowired
	CustomJdbcTemplate jdbcTemplate;
	
	// base column
	private static final String BASE_COLUMN = "SELECT "+
#foreach($field in $fieldList)
#if($!{velocityCount}==$!{fieldList.size()})
#if(${field.columnName}=='create_code')
			" s.${field.columnName}";
			"cu1.`name` AS create_code_name";
#elseif(${field.columnName}=='update_code')
			" s.${field.columnName}";
			"cu2.`name` AS update_code_name ";
#elseif(${field.inputType}=="select")
#if("$!field.inputValueOne" == "")
			" s.${field.columnName}";
			"dic$velocityCount.`name` AS ${field.columnName}_name ";
#else
			" s.${field.columnName}";
			"dic$velocityCount.${field.inputValueTwo} AS ${field.columnName}_name ";
#end
#elseif(${field.columnName}!='update_code'&&${field.columnName}!='create_code'&&${field.inputType}!='select')
			" s.${field.columnName}";
#end
#else
#if(${field.columnName}=='create_code')
			" s.${field.columnName},"+
			"cu1.`name` AS create_code_name,"+
#elseif(${field.columnName}=='update_code')
			" s.${field.columnName},"+
			"cu2.`name` AS update_code_name,"+
#elseif(${field.inputType}=="select")
#if("$!field.inputValueOne" == "")
			" s.${field.columnName},"+
			"dic$velocityCount.`name` AS ${field.columnName}_name,"+
#else
			" s.${field.columnName},"+
			"dic$velocityCount.${field.inputValueTwo} AS ${field.columnName}_name,"+
#end			
#elseif(${field.columnName}!='update_code'&&${field.columnName}!='create_code'&&${field.inputType}!='select')
			" s.${field.columnName},"+
#end
#end 
#end
	
	// base from
		private static final String BASE_FROM = " from ${model.tableName} s "
#foreach($field in $fieldList)
#if(${field.columnName}=='create_code')
			+ "LEFT JOIN cb_user cu1 ON s.create_code=cu1.`code` "
#end
#if(${field.columnName}=='update_code')
			+ "LEFT JOIN cb_user cu2 ON s.update_code=cu2.`code`  "
#end
#if(${field.inputType}=="select")
#if("$!field.inputValueOne" == "")
			+ "LEFT JOIN cb_dictionary dic$velocityCount ON s.${field.columnName}=dic$velocityCount.`code`  "
#else
			+ "LEFT JOIN ${field.inputValueOne} dic$velocityCount ON s.${field.columnName}=dic$velocityCount.`code`  "
#end
#end
#end
			+ "WHERE 1=1 ";

	// list
	private static final String SQL_LIST = "select (@i :=@i + 1) AS i,s.* from ("+BASE_COLUMN + BASE_FROM;
	// list
	private static final String SQL_LIST_ALL = BASE_COLUMN + BASE_FROM;
	
	// count
	private static final String SQL_COUNT = " SELECT count(DISTINCT s.code)" + BASE_FROM;
	
	// sum
	private static final String SQL_SUM = " SELECT count(DISTINCT s.code) "+ BASE_FROM;

	public Integer selectCount(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer(SQL_COUNT);
		// 拼接查询条件
		pageWhereSql(sql, params);
		logger.info("sql:" + sql);
		return jdbcTemplate.queryForObject(sql.toString(), java.lang.Integer.class);
	}

	
	public List<Map<String, Object>> selectListPage(Map<String, Object> params, CustomRowBounds rowBounds) {
		StringBuffer sql = new StringBuffer(SQL_LIST);
		// 拼接查询条件
		pageWhereSql(sql, params);
		
		// 默认ID倒序排列
		sql.append(" ORDER BY " + rowBounds.getColumns() + " " + rowBounds.getDirection());
		// 分页
		int m = (rowBounds.getPageNumber() - 1) * rowBounds.getPageSize();
		int n = rowBounds.getPageSize();
		sql.append(" limit " + m + "," + n + ") as s,(SELECT @i := 0) AS it ");
		logger.info("sql:" + sql);
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString());
		return list;
	}
 
	/**
	 * 组装查询条件
	 * @author ${model.author} ${date}
	 * @param sql
	 * @param params
	 */
	private void pageWhereSql(StringBuffer sql, Map<String, Object> params) {
		// 过滤逻辑删除
		sql.append(" AND s.del_flag = 0 ");
		 	
#foreach($field in $fieldList)
#if($field.matchType == 'dateRange')
		String ${field.field}_start = (String) params.get("${field.field}_start");
		if (!StringUtils.isEmpty(${field.field}_start)) {
			sql.append(" and STR_TO_DATE(s.${field.columnName},'%Y-%m-%d') >= '" + ${field.field}_start + "'");
		}
		String ${field.field}_end = (String) params.get("${field.field}_end");
		if (!StringUtils.isEmpty(${field.field}_end)) {
			sql.append(" and STR_TO_DATE(s.${field.columnName},'%Y-%m-%d') <= '" + ${field.field}_end + "'");
		}
#elseif($field.matchType == 'numberRange')
		String ${field.field}_start = (String) params.get("${field.field}_start");
		if (!StringUtils.isEmpty(${field.field}_start)) {
			sql.append(" and s.${field.columnName} >= '" + ${field.field}_start + "'");
		}
		String ${field.field}_end = (String) params.get("${field.field}_end");
		if (!StringUtils.isEmpty(${field.field}_end)) {
			sql.append(" and s.${field.columnName} <= '" + ${field.field}_end + "'");
		}
#elseif(${field.field}!="id"&&${field.field}!="delFlag")
		String ${field.field}=(String) params.get("${field.field}");
		if(StringUtils.hasText(${field.field})){
#if($field.matchType == 'any')
			sql.append(" and s.${field.columnName} like '%").append(${field.field}).append("%'");
#elseif($field.matchType == 'left')
			sql.append(" and s.${field.columnName} like '").append(${field.field}).append("%'");
#elseif($field.matchType == 'right')
			sql.append(" and s.${field.columnName} like '%").append(${field.field}).append("'");	
#else
			sql.append(" and s.${field.columnName} = '").append(${field.field}).append("' ");
#end
		}
#end
#end
		
		// 分组
		if(StringUtils.hasText((String)params.get("groupByParm"))){
			sql.append(" GROUP BY " + params.get("groupByParm") + " ");
		}	 
	}
	   
 	/**
	 * 根据条件查询所有,非分页
	 * @author ${model.author} ${date}
	 * @param params
	 * @return
	 */
	public List<Map<String, Object>> findAllByParams(Map<String, Object> params){
		StringBuffer sql = new StringBuffer(SQL_LIST_ALL);
		// 拼接查询条件
		pageWhereSql(sql, params); 
		
		// 排列
		if(StringUtils.hasText((String)params.get("orderByParm"))){
			sql.append(" ORDER BY " + params.get("orderByParm") + " ");
		}
		
		
		logger.info("sql:" + sql);
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString());
		return list;
	}
	      
}